from flask import request,jsonify,Blueprint
from db import db1
from comm import get_data

bp_team=Blueprint('team',__name__)
@bp_team.route('/teams',methods=['GET','POST','DELETE','PUT'])
def teams():
    if request.method=='POST':
        data=get_data()
        sql="insert into teamgl(name,bianm,paix,type) values('%s','%s',%d,'%s')"%(data['name'],data['bianm'],int(data['paix']),data['type'])
        res=db1.insert(sql)
        return jsonify({'mes':"添加成功"})
    
    elif request.method=='GET':
        id=request.args.get('id')
        name=request.args.get('name')
        bianm=request.args.get('bianm')
        if id:
            sql="select * from teamgl where id =%d"%(int(id))
            res=db1.find_one(sql)
            return jsonify({'list':res})
        else:
            if name and not bianm:
                print(name)
                sql=f"select * from teamgl where name like '%{name}%'"
                res=db1.find(sql)
                return jsonify({'list':res})
            elif bianm and not name:      
                sql=f"select * from teamgl where bianm like '%{bianm}%'"
                res=db1.find(sql)
                return jsonify({'list':res})
            if name and bianm:
                sql="select * from teamgl where name='%s' || bianm='%s'"%(name,bianm)
                res=db1.find(sql)
                return jsonify({'list':res})
            else:
                sql="select * from teamgl"
                res=db1.find(sql)
                return jsonify({'list':res})

    elif request.method=='DELETE':
        id=request.args.get('id')
        sql="delete from teamgl where id=%d"%int(id)
        res=db1.insert(sql) 
        return jsonify({'mes':"删除成功"})
    else:
        data=get_data()
        print(data)
        sql="update teamgl set name='%s',bianm='%s',paix=%d,type='%s' where id=%d"%(data['name'],data['bianm'],int(data['paix']),data['type'],int(data['id']))
        res=db1.insert(sql)
        return jsonify({'mes':"修改成功"})



from excels import excel
@bp_team.route('/teamdao')
def dao():
    sql="select name,bianm,paix,type from teamgl"
    lst=['组织名称','组织编码','组织排序','组织类型']
    filename='filex.xls'
    excel(sql,lst,filename)
    return jsonify({'url':'http://localhost:5000/static/download/'+filename})
from excels import excelr



@bp_team.route('/teamdaor',methods=['POST'])
def daor():
    file=request.files.get('file')
    file.save('D:\\p9\pro\\workflow\\static\\upload\\'+file.filename)
    filename1='D:\\p9\pro\\workflow\\static\\upload\\'+str(file.filename)
    sql1="insert into teamgl(name,bianm,paix,type) values"
    num1=4
    excelr(sql1,filename1,num1)
    return jsonify({'mes':'成功'})